package cen.tms.ggtour.data;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;



public class DataManager {
	
//singleton pattern
	private static DataManager myData;
	
	private DataManager(Context context) {
		myContext = context;
		open();
	}
	
	public static synchronized DataManager getInstance(Context context){
		if(myData == null){
			myData = new DataManager(context);
		}
		return myData;
	}
	
// end singleton pattern
	
//class data
	public final int T_LOCATION = 0;
	public final int T_BUILDING = 1;
	public final int T_PARKING = 2;
	private static SQLiteDatabase db;
	private static Context myContext;
	
//end class data

//DB Constants
	private static final String DATABASE_NAME = "ggTour";
	private static final int VERSION = 1;
	private static final String EVENTS_TABLE = "Events";
	private static final String LOCATIONS_TABLE = "Locations";
	
//end DB Constants

//initializers


//end initializers
	
	
	private void open(){
		DataOpener open = new DataOpener(myContext);
		if(open.createDB()){
			db = SQLiteDatabase.openDatabase("/data/data/cen.tms.ggtour/databases/ggTour", null, SQLiteDatabase.OPEN_READWRITE);
		}
		//db = open.getWritableDatabase();
	}
	
	public void close(){
		db.close();
		db = null;
		myContext = null;
		myData = null;
	}

//adders
	public void addEvent(String date, String time, String location, String name, String description){
			ContentValues values = new ContentValues();
			if(name != null)
				values.put("name", name);
			if(location != null)
				values.put("location", location);
			if(date != null)
				values.put("date", date);
			if(time != null)
				values.put("time", time);
			if(description != null)
				values.put("description", description);
			db.insert(EVENTS_TABLE, null, values);
	}
	
	public void addLocation(String name, double gps_latitude, double gps_longitude, double proximity_radius, int type, String info){
		ContentValues values = new ContentValues();
		values.put("gps_longitude", gps_longitude);
		values.put("gps_latitude", gps_latitude);
		if(name != null)
			values.put("name", name);
		if(proximity_radius >= 0)
			values.put("proximity_radius", proximity_radius);
		if(info != null)
			values.put("info", info);
		db.insert(LOCATIONS_TABLE, null, values);
}

//end adders
	
//querys
	public Cursor getEvent(int ID) {
		Cursor output = db.query(EVENTS_TABLE, null, EVENTS_TABLE + "._ID = " + ID , null, null, null, null);
		output.moveToFirst();
		return output;
	}
	
	public Cursor getEvents(String date, String time, String location, String name){
		
		String WHERE = "";
		if(date != null)
			WHERE = WHERE +EVENTS_TABLE + ".date = '" + date + "' AND ";
		if(time != null)
			WHERE = WHERE + EVENTS_TABLE + ".time = '" + time + "' AND ";
		if(location != null){
			for(int i = 0; i < location.length(); i++){
				  if(location.charAt(i) == '\''){
					  location = location.substring(0, i) + '\'' + location.substring(i);
					  i++;
				  }
				}
		
			WHERE = WHERE + EVENTS_TABLE + ".location = '" + location + "' AND ";
		}
		if(name != null)
			WHERE = WHERE + EVENTS_TABLE + ".name = '" + name + "' AND ";
		//trim final AND
		if(WHERE.length() >= 4)
			WHERE = WHERE.substring(0, WHERE.length()-4);
		Log.d("DataManager", WHERE);
		//run sql
		Cursor output = db.query(EVENTS_TABLE, null, WHERE, null, null, null, null);
		output.moveToFirst();
		return output;
	}
	
	public Cursor getLocation(int ID){
		Cursor output = db.query(LOCATIONS_TABLE, null, LOCATIONS_TABLE + "._ID = " + ID , null, null, null, null);
		output.moveToFirst();
		return output;
	}
	
	public Cursor getLocations(int type, String name){
		String WHERE = "";
		if(name != null)
			WHERE = WHERE + LOCATIONS_TABLE + ".name = '" + name + "' AND ";
		if(type >= 0)
			WHERE = WHERE + LOCATIONS_TABLE + ".type = " + type + " AND ";
		//trim final AND
		if(WHERE.length() >= 4)
			WHERE = WHERE.substring(0, WHERE.length()-4);
		//run sql
		Cursor output = db.query(LOCATIONS_TABLE, null, WHERE, null, null, null, null);
		output.moveToFirst();
		return output;
	}
	
	public Cursor getLocations(ArrayList<Integer> input){
		String WHERE = LOCATIONS_TABLE + ".name = 'No Location' OR ";
		if(input != null)
		{
			for(int i = 0; i < input.size(); i++){
				WHERE = WHERE + LOCATIONS_TABLE + "._id = " + input.get(i) + " OR ";
			}
		}
		if(WHERE.length() >= 3)
			WHERE = WHERE.substring(0, WHERE.length()-3);
		Cursor output = db.query(LOCATIONS_TABLE, null, WHERE, null, null, null, null);
		return output;
	}
	
//end querys
	
//modifiers
	public void editEvent(int ID, String date, String time, String location, String name, String description){
		ContentValues values = new ContentValues();
		if(name != null)
			values.put("name", name);
		if(location != null)
			values.put("location", location);
		if(date != null)
			values.put("date", date);
		if(time != null)
			values.put("time", time);
		if(description != null)
			values.put("description", description);
		db.update(EVENTS_TABLE, values, EVENTS_TABLE + "._id = " + ID, null);
	}
	
	public void editEvent(String oDate, String oTime, String oLocation, String oName, String nDate, String nTime, String nLocation, String nName, String description){
		String WHERE = "";
		if(oDate != null)
			WHERE = WHERE + EVENTS_TABLE + ".date = '" + oDate + "' AND ";
		if(oTime != null)
			WHERE = WHERE + EVENTS_TABLE + ".time = '" + oTime + "' AND ";
		if(oLocation != null)
			WHERE = WHERE + EVENTS_TABLE + ".location = '" + oLocation + "' AND ";
		if(oName != null)
			WHERE = WHERE + EVENTS_TABLE + ".name = '" + oName + "' AND ";
		//trim final AND
		if(WHERE.length() >= 4)
			WHERE = WHERE.substring(0, WHERE.length()-4);
		//insert values into
		ContentValues values = new ContentValues();
		if(nName != null)
			values.put("name", nName);
		if(nLocation != null)
			values.put("location", nLocation);
		if(nDate != null)
			values.put("date", nDate);
		if(nTime != null)
			values.put("time", nTime);
		if(description != null)
			values.put("description", description);
		db.update(EVENTS_TABLE, values, WHERE, null);
	}
	
	public void editLocation(int ID, String name, double gps_longitude, double gps_latitude, double proximity_radius, int type, String info){
		ContentValues values = new ContentValues();
		values.put("gps_longitude", gps_longitude);
		values.put("gps_latitude", gps_latitude);
		if(name != null)
			values.put("name", name);
		if(proximity_radius >= 0)
			values.put("proximity_radius", proximity_radius);
		if(type >= 0)
			values.put("type", type);
		if(info != null)
			values.put("info", info);
		db.update(LOCATIONS_TABLE, values, LOCATIONS_TABLE + "._id = " + ID, null);
	}
	
	public void editLocation(String oName, int oType, String oLocation, String name, double gps_longitude, double gps_latitude, double proximity_radius, int type, String info){
		String WHERE = "";
		if(oName != null)
			WHERE = WHERE + LOCATIONS_TABLE + ".name = '" + oName + "' AND ";
		if(oType >= 0)
			WHERE = WHERE + LOCATIONS_TABLE + ".type = " + oType + " AND ";
		//trim final AND
		if(WHERE.length() >= 4)
			WHERE = WHERE.substring(0, WHERE.length()-4);
		//insert values into
		ContentValues values = new ContentValues();
		values.put("gps_longitude", gps_longitude);
		values.put("gps_latitude", gps_latitude);
		if(name != null)
			values.put("name", name);
		if(proximity_radius >= 0)
			values.put("proximity_radius", proximity_radius);
		if(type >= 0)
			values.put("type", type);
		if(info != null)
			values.put("info", info);
		db.update(LOCATIONS_TABLE, values, WHERE, null);
	}
	
//end modifiers
	
//removers
	public void deleteEvent(int ID){
		db.delete(EVENTS_TABLE, EVENTS_TABLE + "._ID = " + ID, null);
	}
	
	public void deleteEvents(String date, String time, String location, String name){
		String WHERE = "";
		if(date != null)
			WHERE = WHERE +EVENTS_TABLE + ".date = '" + date + "' AND ";
		if(time != null)
			WHERE = WHERE + EVENTS_TABLE + ".time = '" + time + "' AND ";
		if(location != null)
			WHERE = WHERE + EVENTS_TABLE + ".location = '" + location + "' AND ";
		if(name != null)
			WHERE = WHERE + EVENTS_TABLE + ".name = '" + name + "' AND ";
		//trim final AND
		if(WHERE.length() >= 4)
			WHERE = WHERE.substring(0, WHERE.length()-4);
		//run sql
		db.delete(EVENTS_TABLE, WHERE, null);
	}
	
	public void deleteLocation(int ID){
		db.delete(LOCATIONS_TABLE, LOCATIONS_TABLE + "._ID = " + ID, null);
	}
	
	public void deleteLocation(int type, String name){
		String WHERE = "";
		if(name != null)
			WHERE = WHERE + LOCATIONS_TABLE + ".name = '" + name + "' AND ";
		if(type >= 0)
			WHERE = WHERE + LOCATIONS_TABLE + ".type = " + type + " AND ";
		//trim final AND
		if(WHERE.length() >= 4)
			WHERE = WHERE.substring(0, WHERE.length()-4);
		//run sql
		db.delete(LOCATIONS_TABLE, WHERE, null);
	}
	
//end removers

//data accessors
	public static int getID(Cursor input){
		return input.getInt(input.getColumnIndex("_id"));
	}
	
	public static String getName(Cursor input){
		return input.getString(input.getColumnIndex("name"));
	}
	
	//event accessors
	public static String getDate(Cursor input){
		return input.getString(input.getColumnIndex("date"));
	}
	
	public static String getTime(Cursor input){
		return input.getString(input.getColumnIndex("time"));
	}
	
	public static String getLocation(Cursor input){
		return input.getString(input.getColumnIndex("location"));
	}
	
	public static String getDescription(Cursor input){
		return input.getString(input.getColumnIndex("description"));
	}
	
	//end event accessors
	
	//location accessors
	public static double getLongitude(Cursor input){
		return input.getDouble(input.getColumnIndex("gps_longitude"));
	}
	
	public static double getLatitude(Cursor input){
		return input.getDouble(input.getColumnIndex("gps_latitude"));
	}
	
	public static double getProximityRadius(Cursor input){
		return input.getDouble(input.getColumnIndex("proximity_radius"));
	}
	
	public static int getLocationType(Cursor input){
		return input.getInt(input.getColumnIndex("type"));
	}
	
	public static String getInfo(Cursor input){
		return input.getString(input.getColumnIndex("info"));
	}
	
	//end location acessors
	
//end data accessors
	
//Analytics
	public int countEvents(){
		return db.query(EVENTS_TABLE, null , null, null, null, null, null).getCount();
	}
	
	public int countLocation(){
		return db.query(LOCATIONS_TABLE, null , null, null, null, null, null).getCount();
	}
	
//end Analytics

//begin nested classes
	private class DataOpener extends SQLiteOpenHelper{

		public DataOpener(Context context) {
			super(context, DATABASE_NAME, null, VERSION);
			
		}

		@Override
		public void onCreate(SQLiteDatabase db){
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}
		
		private void copyDB() throws IOException {
			//
			InputStream myInput = myContext.getAssets().open("ggTour");
			OutputStream myOutput = new FileOutputStream("/data/data/cen.tms.ggtour/databases/ggTour"); 
			byte[] buffer = new byte[1024];
	    	int length;
	    	while ((length = myInput.read(buffer))>0){
	    		myOutput.write(buffer, 0, length);
	    	}
	    	//Close the streams
	    	myOutput.flush();
	    	myOutput.close();
	    	myInput.close();
		}
	
	    public boolean createDB(){
		  boolean dbExist = checkDB();
    	  if(dbExist){
    		//do nothing - database already exist
    	  }else{
    		//By calling this method and empty database will be created into the default system path
            //of your application so we are gonna be able to overwrite that database with our database.
        	this.getReadableDatabase();
        	try { 
    			copyDB();
    		} catch (IOException e) {
        		throw new Error("Error copying database");
        	}
        	this.close();
    	}
    	return true;
	}
    private boolean checkDB() {
       SQLiteDatabase checkDB = null;
    	try{
    		checkDB = SQLiteDatabase.openDatabase("/data/data/cen.tms.ggtour/databases/ggTour", null, SQLiteDatabase.OPEN_READONLY);
    	}catch(SQLiteException e){
    		//database does't exist yet
    	}
 
    	if(checkDB != null){
    		checkDB.close();
    	}
    	return checkDB != null ? true : false;
    }
  }
//end nested classes
}